/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.bionic.dao;

import com.bionic.idao.IAccountDAO;
import com.bionic.entity.Account;
import com.bionic.entity.AccountType;
import com.bionic.entity.Currency;
import com.bionic.entity.Users;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import com.bionic.jdbc.connection.DAOFactory;

/**
 *
 * @author Obuhov Denis
 */
public class AccountDAO implements IAccountDAO {

    @Override
    public ArrayList<Account> findAll() {
        final String QUERY = "SELECT acc.id "
                + ",acc.account_no "
                + ",acc.currency_id "
                + ",acc.user_id "
                + ",acc.amount "
                + ",acc.account_type_id "
                + "FROM mydb.account acc";
        ArrayList<Account> accountLst = new ArrayList<Account>();
        Account account = null;
    
        Connection connection = null;
        PreparedStatement ptmt = null;
        ResultSet resultSet = null;

        try {
            connection = DAOFactory.createConnection();
            ptmt = connection.prepareStatement(QUERY);
            resultSet = ptmt.executeQuery();

            while (resultSet.next()) {
                // создали ссылочне объекты, у которых заполнен только ID
                account = new Account(resultSet.getInt("id"));
                account.setAccountNo(resultSet.getString("account_no"));
                account.setCurrencyId(resultSet.getInt("currency_id"));
                account.setUserId(resultSet.getInt("user_id"));
                account.setAmount(resultSet.getBigDecimal("amount"));
                account.setAccountTypeId(resultSet.getInt("account_type_id"));
                // добавили полученный объект в список
                accountLst.add(account);
            }

        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class
                    .getName()).log(Level.SEVERE, null, ex);
        }
        return accountLst;

    }

    @Override
    public Account findById(Integer id) {
        final String QUERY = "SELECT acc.id"
                + ",acc.account_no"
                + ",acc.currency_id"
                + ",acc.user_id"
                + ",acc.amount"
                + ",acc.account_type_id"
                + "FROM mydb.account acc where acc.id = ?";
        Account account = null;
        Users user = null;
        AccountType accountType = null;
        Currency currency = null;

        Connection connection = null;
        PreparedStatement ptmt = null;
        ResultSet resultSet = null;

        try {
            connection = DAOFactory.createConnection();
            ptmt = connection.prepareStatement(QUERY);
            ptmt.setInt(1, id);
            resultSet = ptmt.executeQuery();

            while (resultSet.first()) {
                      // создали ссылочне объекты, у которых заполнен только ID
                account = new Account(resultSet.getInt("id"));
                account.setAccountNo(resultSet.getString("account_no"));
                account.setCurrencyId(resultSet.getInt("currency_id"));
                account.setUserId(resultSet.getInt("user_id"));
                account.setAmount(resultSet.getBigDecimal("amount"));
                account.setAccountTypeId(resultSet.getInt("account_type_id"));
            }

        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class
                    .getName()).log(Level.SEVERE, null, ex);
        }
        System.out.println("  Account findById\n"+account);
        return account;

    }

    @Override
    public Integer update(Account account) {
        final String QUERY = "UPDATE mydb.account acc"
                + "SET acc.account_no ?"
                + ",acc.currency_id = ?"
                + ",acc.user_id = ?"
                + ",amount = ?"
                + ",account_type_id = ?"
                + "WHERE acc.id = ?";
        Integer rowUpdate = null;
        Users someUser = null;
        AccountType accountType = null;
        Currency currency = null;

        Connection connection = null;
        PreparedStatement ptmt = null;
        ResultSet resultSet = null;

        try {
            connection = DAOFactory.createConnection();
            ptmt = connection.prepareStatement(QUERY);
            ptmt.setString(1, account.getAccountNo());
            ptmt.setInt(2, account.getCurrencyId());
            ptmt.setInt(3, account.getUserId());
            ptmt.setBigDecimal(4, account.getAmount());
            ptmt.setInt(5, account.getAccountTypeId());
            ptmt.setInt(6, account.getId());

            rowUpdate = ptmt.executeUpdate();

        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class
                    .getName()).log(Level.SEVERE, null, ex);
        }
        System.out.println("  Account update rowUpdate="+rowUpdate+"\n"+account);        
        return rowUpdate;
    }

    @Override
    public Integer insert(Account account) {
        final String QUERY =
                "INSERT INTO mydb.account"
                + "(id,account_no,currency_id,user_id,amount,account_type_id)"
                + " VALUES (account_seq(),?,?,?,?,?)";
        Integer rowInsert = null;
        Users someUser = null;
        AccountType accountType = null;
        Currency currency = null;

        Connection connection = null;
        PreparedStatement ptmt = null;
        ResultSet resultSet = null;

        try {
            connection = DAOFactory.createConnection();
            ptmt = connection.prepareStatement(QUERY);
            ptmt.setString(1, account.getAccountNo());
            ptmt.setInt(2, account.getCurrencyId());
            ptmt.setInt(3, account.getUserId());
            ptmt.setBigDecimal(4, account.getAmount());
            ptmt.setInt(5, account.getAccountTypeId());
       
            rowInsert = ptmt.executeUpdate();

        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class
                    .getName()).log(Level.SEVERE, null, ex);
        }
        System.out.println("  Account insert rowUpdate="+rowInsert+"\n"+account); 
        return rowInsert;
    }

    @Override
    public Integer deleteById(Integer id) {
        final String QUERY = "delete from mydb.account "
                + " WHERE id = ?";
        Integer rowDelete = null;

        Connection connection = null;
        PreparedStatement ptmt = null;
        ResultSet resultSet = null;

        try {
            connection = DAOFactory.createConnection();
            ptmt = connection.prepareStatement(QUERY);
            ptmt.setInt(1, id);

            rowDelete = ptmt.executeUpdate();

        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class
                    .getName()).log(Level.SEVERE, null, ex);
        }
        System.out.println("  Account update rowUpdate=" + rowDelete);
        return rowDelete;
    }

    @Override
    public ArrayList<Account> findByUserId(Integer userId)  {
        final String QUERY = "SELECT acc.id "
                + ",acc.account_no "
                + ",acc.currency_id "
                + ",acc.user_id "
                + ",acc.amount "
                + ",acc.account_type_id "
                + "FROM mydb.account acc where acc.user_id = ?";
        ArrayList<Account> accountLst = new ArrayList<Account>();
        Account account = null;
     
        Connection connection = null;
        PreparedStatement ptmt = null;
        ResultSet resultSet = null;

        try {
            connection = DAOFactory.createConnection();
            ptmt = connection.prepareStatement(QUERY);
            ptmt.setInt(1, userId);
            resultSet = ptmt.executeQuery();

            while (resultSet.next()) {
                // создали ссылочне объекты, у которых заполнен только ID
                account = new Account(resultSet.getInt("id"));
                account.setAccountNo(resultSet.getString("account_no"));
                account.setCurrencyId(resultSet.getInt("currency_id"));
                account.setUserId(resultSet.getInt("user_id"));
                account.setAmount(resultSet.getBigDecimal("amount"));
                account.setAccountTypeId(resultSet.getInt("account_type_id"));
                // добавили полученный объект в список
                accountLst.add(account);
            }

        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class
                    .getName()).log(Level.SEVERE, null, ex);
        }
        return accountLst;
    }
}
